using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class zTableFieldDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[proczTableField_add]";
        private string _strSPUpdateName = "dbo.[proczTableField_update]";
        private string _strSPDeleteName = "dbo.[proczTableField_delete]";
        private string _strSPGetName = "dbo.[proczTableField_get]";
        private string _strSPGetAllName = "dbo.[proczTableField_getall]";
        private string _strSPGetPages = "dbo.[proczTableField_getpaged]";
        private string _strSPIsExist = "dbo.[proczTableField_isexist]";
        private string _strTableName = "zTableField";
        private string _strSPGetComboTable = "dbo.[proczTableField_GetComboTable]";
        private string _strSPGetComboField = "dbo.[proczTableField_GetComboField]";
        private string _strSPGetRuleSet = "dbo.[proczTableField_GetRuleSet]";
        private string _strSPGetRuleSetField = "proczTableField_GetFieldRuleSet";
        #endregion Local Variable

        #region Method
        public zTableFieldInfo Get_zTableField(
            Int32 TableFieldID
        , ref string sErr)
        {
            zTableFieldInfo objEntr = new zTableFieldInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("TableFieldID", TableFieldID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = new zTableFieldInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }


        public DataTable GetAll_zTableField(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable Get_zTableName(string TableName, ref string sErr)
        {
            return Get_Field(TableName, ref sErr);
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_zTableField(zTableFieldInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter("TableFieldID", objEntr.TableFieldID);
            AddParameter("TableName", objEntr.TableName);
            AddParameter("FieldName", objEntr.FieldName);
            AddParameter("FormName", objEntr.FormName);
            AddParameter("FormCaption", objEntr.FormCaption);
            AddParameter("BoxName", objEntr.BoxName);
            AddParameter("LabelName", objEntr.LabelName);
            AddParameter("LabelCaption", objEntr.LabelCaption);
            AddParameter("SourceName", objEntr.SourceName);
            AddParameter("Description1", objEntr.Description1);
            AddParameter("Filter", objEntr.Filter);
            AddParameter("RuleSet", objEntr.RuleSet);
            AddParameter("Security", objEntr.Security);
            AddParameter("BoxType", objEntr.BoxType);
            AddParameter("Validated", objEntr.Validated);
            AddParameter("SetRowsource", objEntr.SetRowsource);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_zTableField(zTableFieldInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("TableFieldID", objEntr.TableFieldID);
            AddParameter("TableName", objEntr.TableName);
            AddParameter("FieldName", objEntr.FieldName);
            AddParameter("FormName", objEntr.FormName);
            AddParameter("FormCaption", objEntr.FormCaption);
            AddParameter("BoxName", objEntr.BoxName);
            AddParameter("LabelName", objEntr.LabelName);
            AddParameter("LabelCaption", objEntr.LabelCaption);
            AddParameter("SourceName", objEntr.SourceName);
            AddParameter("Description1", objEntr.Description1);
            AddParameter("Filter", objEntr.Filter);
            AddParameter("RuleSet", objEntr.RuleSet);
            AddParameter("Security", objEntr.Security);
            AddParameter("BoxType", objEntr.BoxType);
            AddParameter("Validated", objEntr.Validated);
            AddParameter("SetRowsource", objEntr.SetRowsource);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_zTableField(
            Int32 TableFieldID
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter("TableFieldID", TableFieldID);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        

        public Boolean IsExist_zTableField(
            Int32 TableFieldID
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);

            AddParameter("TableFieldID", TableFieldID);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(zTableFieldInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

        public DataTable Get_Combobox(string BoxType, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetComboTable);
            AddParameter("BoxType", BoxType);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable Get_ComboboxField(string TableID, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetComboField);
            AddParameter("TableName", TableID);
            AddParameter("BoxType", "C");
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetRuleSet(string RuleSet, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetRuleSet);
            AddParameter("RuleSet", RuleSet);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetRuleSet(string TableName, string RuleSet, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetRuleSetField);
            AddParameter("RuleSet", RuleSet);
            AddParameter("TableName", TableName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable Get_Field(string TableName, ref string sErr, string FormName = null, string BoxType = null)
        {
            InitConnect();
            string sql = @"SELECT DISTINCT FieldName, LabelCaption, TableName, BoxType,Description1
		        FROM zTableField 
                WHERE TableName = @TableName ";
            if (FormName != null) sql += " AND FormName = @FormName ";
            if (BoxType != null) sql += " AND BoxType = @BoxType ";
            sql += " ORDER BY Description1 ";

            InitCommand(sql, CommandType.Text);

            AddParameter("TableName", TableName);
            if (FormName != null) AddParameter("FormName", FormName);
            if (BoxType != null) AddParameter("BoxType", BoxType);

            DataTable list = new DataTable();
            try { list = executeSelect(); }
            catch (Exception ex) { sErr = ex.Message; }

            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable Get_zTableName(string TableName, string FormName, ref string sErr)
        {
            return Get_Field(TableName, ref sErr, FormName);
        }
    }
}
